package com.yc.SelectCourse.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.yc.SelectCourse.biz.BizException;
import com.yc.shopping.util.DBHelper;
import com.yc.shopping.util.DBHelper.Page;

public class SelectCourseDao {
	
	public List<Map<String, Object>> findAllCourse() throws BizException{
		String sql = "select cid,cname,ccreadit,tname,classroom,c.tid from course c "
				+ " inner join teacher t on c.tid=t.tid";
		try {
			return DBHelper.selectListMap(sql);
		} catch (SQLException e) {
			
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
	}
	
	public Map<String,Object> findTeaherID(String tname) throws BizException{
		String sql = "select * from teacher where tname = ? ";
		try {
			return DBHelper.selectOneMap(sql,tname);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("查无此人",e);
		}
	}
	//查询课程 数量 以便分页
	public int findDateNum() throws BizException{
		String sql = "select count(1) c from course ";
		
		try {
			return DBHelper.selectListMap(sql).size();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("未知异常",e);
		}
	}
	
	//组合条件查询
	public List<Map<String, Object>> findCourse (String type ,String nrong) throws BizException {
		//条件查询
		String sql = "select cid,cname,ccreadit,tname,classroom from course c "
				+ " inner join teacher t on c.tid=t.tid"
				+ " where 1=1 ";

		if("全部".equals(type)) {
			try {
				return DBHelper.selectListMap(sql);
			} catch (SQLException e) {	
				e.printStackTrace();
				throw new BizException("业务异常,请联系管理员",e);
			}
		}
		if("课程编号".equals(type)) {
			sql+=" and cid = ?";

		}else if("课程名称".equals(type)) {
			sql+=" and cname like '%' ||?|| '%'";

		}else if("任课教师".equals(type)) {
			sql+= " and  tname like  '%' ||?|| '%' ";

		}else if("学分".equals(type)) {
			sql+= " and  ccreadit = ? ";

		}
		sql+=" order by cid";
		try {
			return DBHelper.selectListMap(sql, nrong);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
	}
	
	
	//添加课程
	public void add(String sid,String cid) throws BizException {
		String sql = "insert into student_course values(?,?)";
		try {
			DBHelper.update(sql, sid,cid);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("选课异常,请联系管理员",e);
		}
	}
	
	//查询每门课人数
	public Map<String,Object> selectNum(String cid) throws BizException {
		String sql = "select  cid,count(*) num from student_course  where cid = ? group by cid";
		try {
			return DBHelper.selectOneMap(sql, cid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
	}
	
	public List<Map<String, Object>> selectPage(int page,int size) throws BizException{
		String sql="select *   from (select a.*, rownum rn from (select * from  course c inner join teacher t on c.tid=t.tid) a where rownum <= ?) a \twhere a.rn > ?";
		int start = (page-1)*size;
		int end = start + size;
		try {
			return DBHelper.selectListMap(sql,end,start );
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
	}
	
	//查询已选课表
	//实现不能重复选课
	public int courseExsit(String cid) throws BizException{
		String sql = "select * from student_course where sid = ? and cid= ? ";
		try {
			//10011001001 是用户ID 改为StuUtils.user.get(SID).toString();
			return DBHelper.selectListMap(sql,"10011001001",cid).size();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
	}
	
	//查询该学生选课数目
	public int  selectedCourseNumber() throws BizException {
		String sql = "select * from student_course where sid = ? ";
		try {
			return DBHelper.selectListMap(sql, "10011001001").size();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BizException("业务异常,请联系管理员",e);
		}
		
	}
}
